﻿alter table Cms_Wsm_AccessoryStock
add Deleted bit not null default(0)

go

alter table Cms_Wsm_TaskRegister add IsSystemAssign bit
go
alter table Cms_Wsm_InstrumentAssembly add AdjustMan uniqueidentifier
go
alter table Cms_Wsm_InstrumentAssembly add ConservationMan uniqueidentifier
go
alter table Cms_Wsm_InstrumentAssembly add ReturnVisitMan uniqueidentifier
go
update  Cms_Wsm_TaskRegister set IsSystemAssign =  0
update  Cms_Wsm_TaskRegister set IsSystemAssign =  1 where tasktype in(3,6,7)  and  CreatedBy = '11111111-1111-1111-1111-111111111111'

go

update Cms_Wsm_InstrumentAssembly
set AdjustMan = b.Maintainer,
	ConservationMan = b.Maintainer,
	ReturnVisitMan = b.Maintainer
from Cms_Wsm_InstrumentAssembly a
inner join Cms_Wsm_MaintainBill b on b.MaintainID = a.MaintainID

go

alter  proc [dbo].[CreateBillForCycle]
as
begin

DECLARE @CustomerID uniqueidentifier
		,@TaskType int		 
		,@AdjustMan uniqueidentifier
		,@ConservationMan uniqueidentifier
		,@ReturnVisitMan uniqueidentifier
		,@InstrumentID uniqueidentifier			 
		,@InstrumentType uniqueidentifier
		,@InstrumentSerialNum varchar(50)
		,@GuaranteeFrequency decimal(18,2)
		,@ReturnVisitFrequency decimal(18,2)	 
		,@CalibrationFrequency decimal(18,2)
		,@InstrumentBeginUse datetime

		declare @GuaranteebeginDate datetime
		declare @ReturnVisitbeginDate varchar(50)
		declare @CalibrationbeginDate varchar(50)
				
	DECLARE cur_bill CURSOR FOR 	
		SELECT 		 				 
			a.CustomerID
			,a.TaskType			 
			,c.AdjustMan
			,c.ConservationMan
			,c.ReturnVisitMan		
			,a.InstrumentID		
			,a.InstrumentType
			,a.InstrumentSerialNum
			,case when isnull(c.GuaranteeFrequency,0) = 0 then 120 else c.GuaranteeFrequency end GuaranteeFrequency
			,case when isnull(c.ReturnVisitFrequency,0) = 0 then 120 else c.ReturnVisitFrequency end ReturnVisitFrequency
			,case when isnull(c.CalibrationFrequency,0) = 0 then 120 else c.CalibrationFrequency end CalibrationFrequency
			,c.InstrumentBeginUse
			,c.GuaranteebeginDate
			,c.ReturnVisitbeginDate
			,c.CalibrationbeginDate
		FROM Cms_Wsm_TaskRegister as a 
		inner join Cms_Wsm_MaintainBill b on a.RegisterID = b.RegisterID
		inner join Cms_Wsm_InstrumentAssembly c 
		on c.MaintainID = b.MaintainID
		where a.TaskType = 1 and a.TaskStatus = 1 --and b.AccessoryCheckStatus = 2
		
	OPEN cur_bill
	FETCH next FROM cur_bill
	INTO @CustomerID,@TaskType,@AdjustMan,@ConservationMan,@ReturnVisitMan,@InstrumentID,@InstrumentType,
			@InstrumentSerialNum,@GuaranteeFrequency,@ReturnVisitFrequency,
			@CalibrationFrequency,@InstrumentBeginUse
			,@GuaranteebeginDate
			,@ReturnVisitbeginDate
			,@CalibrationbeginDate
	
	WHILE @@FETCH_STATUS = 0
	BEGIN
		
		declare @DateNow varchar(50)
		
		set @DateNow = convert(varchar(12), GETDATE(),112)
		IF (convert(char(10),@GuaranteebeginDate,121)> '1980-01-01' and (DATEDIFF(month,@GuaranteebeginDate,@DateNow) % @GuaranteeFrequency) = 0 and DATEDIFF(month,@GuaranteebeginDate,getdate()) > 0 and Substring(cast(@GuaranteeFrequency as varchar),CHARINDEX('.',cast(@GuaranteeFrequency as varchar))+1,Len(cast(@GuaranteeFrequency as varchar))-CHARINDEX('.',cast(@GuaranteeFrequency as varchar)))='00' )
			BEGIN 
				 insert into Cms_Wsm_TaskRegister values(NEWID(),'11111111-1111-1111-1111-111111111111',null,3,0,@CustomerID,dateadd(month,DATEDIFF(month,@GuaranteebeginDate,@DateNow),@GuaranteebeginDate),
				 @ConservationMan,GETDATE(),null,@InstrumentID,@InstrumentType,@InstrumentSerialNum,0,GETDATE(),1)
			END 
		IF (convert(char(10),@ReturnVisitbeginDate,121)> '1980-01-01' and (DATEDIFF(month,@ReturnVisitbeginDate,@DateNow) % @ReturnVisitFrequency) = 0  and DATEDIFF(month,@ReturnVisitbeginDate,getdate()) > 0 and Substring(cast(@ReturnVisitFrequency as varchar),CHARINDEX('.',cast(@ReturnVisitFrequency as varchar))+1,Len(cast(@ReturnVisitFrequency as varchar))-CHARINDEX('.',cast(@ReturnVisitFrequency as varchar)))='00')
			BEGIN 
				 insert into Cms_Wsm_TaskRegister values(NEWID(),'11111111-1111-1111-1111-111111111111',null,7,0,@CustomerID,dateadd(month,DATEDIFF(month,@ReturnVisitbeginDate,@DateNow),@ReturnVisitbeginDate),
				 @ReturnVisitMan,GETDATE(),null,@InstrumentID,@InstrumentType,@InstrumentSerialNum,0,GETDATE(),1)
			END 
		IF (convert(char(10),@CalibrationbeginDate,121)> '1980-01-01' and (DATEDIFF(month,@InstrumentBeginUse,@DateNow) % @CalibrationFrequency) = 0 and DATEDIFF(month,@InstrumentBeginUse,getdate()) > 0 and Substring(cast(@CalibrationFrequency as varchar),CHARINDEX('.',cast(@CalibrationFrequency as varchar))+1,Len(cast(@CalibrationFrequency as varchar))-CHARINDEX('.',cast(@CalibrationFrequency as varchar)))='00')
			BEGIN 
				 insert into Cms_Wsm_TaskRegister values(NEWID(),'11111111-1111-1111-1111-111111111111',null,6,0,@CustomerID,dateadd(month,DATEDIFF(month,@CalibrationbeginDate,@DateNow),@CalibrationbeginDate),
				 @AdjustMan,GETDATE(),null,@InstrumentID,@InstrumentType,@InstrumentSerialNum,0,GETDATE(),1)
			END 
		FETCH next FROM cur_bill
			INTO @CustomerID,@TaskType,@AdjustMan,@ConservationMan,@ReturnVisitMan,@InstrumentID,@InstrumentType,
				@InstrumentSerialNum,@GuaranteeFrequency,@ReturnVisitFrequency,
				@CalibrationFrequency,@InstrumentBeginUse,@GuaranteebeginDate
			,@ReturnVisitbeginDate
			,@CalibrationbeginDate
	END
	CLOSE cur_bill
	DEALLOCATE cur_bill
end


